import os
import nltk
import pymysql
import time

stopwords = ['','出差补贴','实施','周末双休','带薪年假','计算机','K','H','D','实施','实施顾问','需求分析','A','W','年终双薪','年终双薪',
             '奖金丰厚','交通补助','银行','加班补助','P','话补','高级','U','人才推荐奖','全勤奖','E','I','项目','绩效奖金', '高温补贴',
             '应届毕业生', '车载', '销售代表','保险', '渠道销售','物流', 'M','应届', 'V','转行','G', 'N', '金蝶','医疗','五险一金',
             '物流仓管配送', '化工生物技术', '机械机电维修','水利水电桥梁', '交易所','年终分红','中间件','专业培训','P', '数字贷币',
             '技术支持','O','J','D','P','B','S','T', 'My', '日企', '用友', '定期体检','应届生','员工旅游', 'F','咨询', '电力','Three',
             '软件工程师','工程师','0基础','节日福利','文员','技术','双休','视频','立即上岗']

# 这是第一次提取数据库关键字进行关键词计算
def readKeywords():
    dictionary = {}  # 空词典，用于保存最终的词频
    # 连接数据库
    connpool = pymysql.connect(host='101.132.137.72',
                               port=3306,
                               user='root',
                               password='wxe',
                               db='recruitment',
                               charset='utf8')
    cursor = connpool.cursor()  # 执行完毕返回的结果集默认以元组显示
    # 读取数据
    dataAll=None
    try:
        sql = "select keyword FROM job_info"
        cursor.execute(sql)
        dataAll = cursor.fetchall()
    except MemoryError:
        print("cuowu")
    connpool.close()

    # 词频统计
    for data in dataAll:
        data = " ".join(data)
        data = data.split(',')
        fredist = nltk.FreqDist(data)
        for localkey in fredist.keys():
            if localkey in stopwords:  # 检查是否为停用词
                continue
            try:
                localkey = localkey.title()
            except SyntaxError:
                pass
            if localkey in dictionary.keys():  # 检查当前词频是否在字典中存在
                dictionary[localkey] = dictionary[localkey] + fredist[localkey]  # 如果存在，将词频累加，并更新字典值
                # print('--> 重复值：', localkey, dictionary[localkey])
            else:  # 如果字典中不存在
                dictionary[localkey] = fredist[localkey]  # 将当前词频添加到字典中
                # print('--> 新增值：', localkey, dictionary[localkey])
            # print('===================================================')
    # print(sorted(dictionary.items(), key=lambda x: x[1]))  # 根据词频字典值排序，并打印

    # 写入到数据库
    connpool = pymysql.connect(host='101.132.137.72',
                               port=3306,
                               user='root',
                               password='wxe',
                               db='recruitment',
                               charset='utf8')
    cursor = connpool.cursor()  # 执行完毕返回的结果集默认以元组显示
    for key in dictionary:
        if( dictionary[key] < 50 ):
            continue
        print(key, dictionary[key])
        cursor.execute(
            "insert into keyWords (word,total)"
            " values(%s, %s)",(key, dictionary[key]))
        connpool.commit()
    connpool.close()

# readKeywords()

# 这是更新关键字词库的代码
def updateKeywords():
    # oldDictionary = getOldDictionary()
    # print(oldDictionary)
    ti = time.strftime('%m-%d', time.localtime())
    ti = "11-04"
    print(ti, type(ti))
    connpool = pymysql.connect(host='101.132.137.72',
                               port=3306,
                               user='root',
                               password='wxe',
                               db='recruitment',
                               charset='utf8')
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标
    cursor = connpool.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        sql = "select keyword FROM job_info WHERE pubdate = '" + ti + "'"
        print(sql)
        cursor.execute(sql)
        dict = cursor.fetchall()
    except pymysql.Error as e:
        print(e)
    connpool.close()
    for da in dict:
        print(da)

def getOldDictionary():
    connpool = pymysql.connect(host='101.132.137.72',
                               port=3306,
                               user='root',
                               password='wxe',
                               db='recruitment',
                               charset='utf8')
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标
    cursor = connpool.cursor(cursor=pymysql.cursors.DictCursor)
    try:
        sql = "select keyWord, count FROM keyWords"
        cursor.execute(sql)
        dict = cursor.fetchall()
    except MemoryError:
        print("cuowu")
    connpool.close()
    # print(dict)
    oldDictionary = {}  # 旧的关键字字典
    for data in dict:
        oldDictionary[data["keyWord"]] = data["count"]
    return oldDictionary

updateKeywords()
